<?php

namespace App\Front;

use App\Http\Controllers\Controller;
use App\Models\Appeal;
use App\Models\Consultation;
use App\Models\Report;
use App\Models\Village;
use Auth;
use DB;
use Illuminate\Http\Request;

class GeotherController extends Controller
{
    //小程序  我的统计 接口
    public function sortByVillage(Request $request)
    {
        //type: "todo", vid: "1"
        $village_id = $request->vid;
        $state = $request->type === "done" ? true : false;

        $wxuser = Auth::user();
        //获取当前用户管辖的村（当前用户作为管理员，村可能有多个）
        $count = $wxuser->villages()->count();

        //村民
        if ($count == 0) {
            $consultations = Consultation::where("village_id", "=", $village_id)
                ->where("state", $state)->where('wxuser_id', "=", $wxuser->id)
                ->select('id', 'detail', 'category', 'created_at')
                ->get()->makeHidden('village_name');

            $appeals = Appeal::where('village_id', "=", $village_id)
                ->where("state", $state)->where('wxuser_id', "=", $wxuser->id)
                ->select('id', 'detail', 'category', 'created_at')
                ->get()->makeHidden('village_name');

            $reports = Report::where('village_id', "=", $village_id)
                ->where("state", $state)->where("wxuser_id", "=", $wxuser->id)
                ->select('id', 'detail', 'category', 'created_at')
                ->get()->makeHidden('village_name');
        }
        //村干部
        if ($count > 0) {

            $consultations = Consultation::where("village_id", "=", $village_id)
                ->select('id', 'detail', 'category', 'created_at')
                ->where("state", "=", $state)->get()->makeHidden('village_name');

            $appeals = Appeal::where('village_id', "=", $village_id)
                ->select('id', 'detail', 'category', 'created_at')
                ->where("state", "=", $state)->get()->makeHidden('village_name');

            $reports = Report::where('village_id', "=", $village_id)
                ->select('id', 'detail', 'category', 'created_at')
                ->where("state", "=", $state)->get()->makeHidden('village_name');
        }

        //返回当前村的3个事务（政策咨询、举报干部、反应诉求），按照村分类
        return [
            'consultations' => $consultations,
            'appeals' => $appeals,
            'reports' => $reports
        ];

    }

    //待处理、已处理(我的页面，显示条数)
    public function deals()
    {
        //当前登录用户
        $wxuser = Auth::user();
        //当前用户作为村干部
        $count = $wxuser->villages()->count();

        if ($count == 0) {
            //登录用户不是村干部。仅显示自己曾经提交的（待处理与已处理）
            //待处理
            $cAll = $wxuser->consultations()->count();
            $aAll = $wxuser->appeals()->count();
            $rAll = $wxuser->reports->count();
            $cDones = $wxuser->consultations()->where("state", true)->count();
            $aDones = $wxuser->appeals()->where('state', true)->count();
            $rDones = $wxuser->reports()->where('state', true)->count();
            return [
                'isDone' => $cDones + $aDones + $rDones,
                'willDone' => ($cAll - $cDones) + ($aAll - $aDones) + ($rAll - $rDones)
            ];
        }

        //当前登录用户为村干部
        if ($count !== 0) {
            //我管辖的  村
            $villages = Auth::user()->villages()->get();
            $vids = collect($villages)->map(function ($item) {
                return $item->id;
            });

            $cAll = Consultation::whereIn('village_id', $vids)->count();
            $aAll = Appeal::whereIn('village_id', $vids)->count();
            $rAll = Report::whereIn('village_id', $vids)->count();


            $cDones = Consultation::whereIn('village_id', $vids)->where('state', true)->count();
            $aDones = Appeal::whereIn('village_id', $vids)->where('state', true)->count();
            $rDones = Report::whereIn('village_id', $vids)->where('state', true)->count();
            return [
                'isDone' => $cDones + $aDones + $rDones,
                'willDone' => ($cAll - $cDones) + ($aAll - $aDones) + ($rAll - $rDones)
            ];
        }

    }

    //各村统计(待处理) 列表页
    public function perVillageTodo()
    {
        $wxuser = Auth::user();
        //当前登录用户
        $count = $wxuser->villages()->count();

//$count>0  村干部
        if ($count > 0) {

            $vids = $wxuser->villages->pluck('id')->toArray();

            return $this->queryResult($vids, true, false);
        }

        if ($count == 0) {
            //村民 找出我曾今发过的数组 政策咨询cids，反应诉求aids，举报干部 的rids
            return $this->queryResult($wxuser->id, false, false);
        }
    }

    //各村统计(已处理) 列表页
    public function perVillageDone()
    {
        $wxuser = Auth::user();
        //当前登录用户 是否为
        $count = $wxuser->villages()->count();

//$count>0  村干部
        if ($count > 0) {

            $vids = $wxuser->villages->pluck('id')->toArray();

            return $this->queryResult($vids, true, true);
        }
//村民
        if ($count == 0) {
            //村民 找出我曾今发过的数组 政策咨询cids，反应诉求aids，举报干部 的rids
            return $this->queryResult($wxuser->id, false, true);
        }
    }


    //传递一个身份的参数，第一个参数是身份（包含wxuser表示村民，第二个参数（是否为管理员）,第三个参数待处理（已处理））
    protected function queryResult($queryString = [], $isAdmin = true, $isDone = true)
    {
        if ($isAdmin) {
            $consultationQuery = Consultation::selectRaw("village_id,count(*) as todo")->whereIn("village_id", $queryString)->where('state', "=", $isDone)->groupBy('village_id');
            $appealQuery = Appeal::selectRaw("village_id,count(*) as todo")->whereIn("village_id", $queryString)->where('state', "=", $isDone)->groupBy('village_id');
            $reportQuery = Report::selectRaw("village_id,count(*) as todo")->whereIn("village_id", $queryString)->where('state', "=", $isDone)->groupBy('village_id');
        } else {
            $consultationQuery = Consultation::selectRaw("village_id,count(*) as todo")->where('wxuser_id', "=", $queryString)->where('state', "=", $isDone)->groupBy('village_id');
            $appealQuery = Appeal::selectRaw("village_id,count(*) as todo")->where('wxuser_id', "=", $queryString)->where('state', "=", $isDone)->groupBy('village_id');
            $reportQuery = Report::selectRaw("village_id,count(*) as todo")->where('wxuser_id', "=", $queryString)->where('state', "=", $isDone)->groupBy('village_id');
        }


        $result1 = Village::selectRaw('v.id,v.name,IFNULL( c.todo, 0 ) AS number')
            ->from('villages as v')
            ->leftJoin(\DB::raw("({$consultationQuery->toSql()}) as c"), function ($join) use ($consultationQuery) {
                //toSql()返回的是等待绑定参数的SQL语句
                $join->mergeBindings($consultationQuery->getQuery())->on('v.id', '=', 'c.village_id');
                //mergeBindings是将SQl的参数进行绑定
            })
            ->get()->makeHidden("admins")->toArray();

        $result2 = Village::selectRaw('v.id,v.name,IFNULL( c.todo, 0 ) AS number')
            ->from('villages as v')
            ->leftJoin(\DB::raw("({$appealQuery->toSql()}) as c"), function ($join) use ($appealQuery) {
                $join->mergeBindings($appealQuery->getQuery())->on('v.id', '=', 'c.village_id');
            })
            ->get()->makeHidden("admins")->toArray();

        $result3 = Village::selectRaw('v.id,v.name,IFNULL( c.todo, 0 ) AS number')
            ->from('villages as v')
            ->leftJoin(\DB::raw("({$reportQuery->toSql()}) as c"), function ($join) use ($reportQuery) {
                $join->mergeBindings($reportQuery->getQuery())->on('v.id', '=', 'c.village_id');
            })
            ->get()->makeHidden("admins")->toArray();

        $collection = collect([$result2, $result3]);

        $collection->each(function ($item) use (&$result1) {
            collect($item)->each(function ($data) use (&$result1) {
                collect($result1)->each(function ($res, $key) use ($data, &$result1) {
                    if ($res['id'] == $data["id"]) {
                        $result1[$key]['number'] += $data['number'];
                    }
                });
            });
        });

        return $result1;
    }
}
